using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using T2.WebSecurity.Data.Entities;
using T2.WebSecurity.Data.Entities.Interfaces;
using T2.WebSecurity.Data.Controllers.Interfaces;
using T2.WebSecurity.Data.Entities.Search.Interfaces;
using T2.WebSecurity.Data.Entities.Search;

namespace T2.WebSecurity.Data.Controllers
{
	public partial class UserProfileController : IUserProfileController
	{
	private IUserProfile TranslateUserProfileFromDb(DataRow  _translateFrom)
	{
		IUserProfile temp = new UserProfile();
		temp.Action = T2.WebSecurity.Common.MyEnum.Actions.None.ToString();
		if(_translateFrom["UserId"] != null && _translateFrom["UserId"].ToString().Length > 0)
		{
		temp.UserId = Convert.ToInt32(_translateFrom["UserId"]);
		}
		else
		{
		temp.UserId = Convert.ToInt32("-1");
		}
		if(_translateFrom["UserName"] != null && _translateFrom["UserName"].ToString().Length > 0)
		{
		temp.UserName = Convert.ToString(_translateFrom["UserName"]);
		}
		else
		{
		temp.UserName = String.Empty;
		}
		temp.TS = (Byte[])_translateFrom["TS"];
		if(_translateFrom["CreatedBy"] != null && _translateFrom["CreatedBy"].ToString().Length > 0)
		{
		temp.CreatedBy = Convert.ToString(_translateFrom["CreatedBy"]);
		}
		else
		{
		temp.CreatedBy = String.Empty;
		}
		if(_translateFrom["CreatedDt"] != null && _translateFrom["CreatedDt"].ToString().Length > 0)
		{
		temp.CreatedDt = Convert.ToDateTime(_translateFrom["CreatedDt"]);
		}
		else
		{
		temp.CreatedDt = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["ModifiedBy"] != null && _translateFrom["ModifiedBy"].ToString().Length > 0)
		{
		temp.ModifiedBy = Convert.ToString(_translateFrom["ModifiedBy"]);
		}
		else
		{
		temp.ModifiedBy = String.Empty;
		}
		if(_translateFrom["ModifiedDt"] != null && _translateFrom["ModifiedDt"].ToString().Length > 0)
		{
		temp.ModifiedDt = Convert.ToDateTime(_translateFrom["ModifiedDt"]);
		}
		else
		{
		temp.ModifiedDt = Convert.ToDateTime("01/01/1753");
		}
		if(_translateFrom["IsActive"] != null && _translateFrom["IsActive"].ToString().Length > 0)
		{
		temp.IsActive = Convert.ToBoolean(_translateFrom["IsActive"]);
		}
		if(_translateFrom["FirstName"] != null && _translateFrom["FirstName"].ToString().Length > 0)
		{
		temp.FirstName = Convert.ToString(_translateFrom["FirstName"]);
		}
		else
		{
		temp.FirstName = String.Empty;
		}
		if(_translateFrom["LastName"] != null && _translateFrom["LastName"].ToString().Length > 0)
		{
		temp.LastName = Convert.ToString(_translateFrom["LastName"]);
		}
		else
		{
		temp.LastName = String.Empty;
		}
		if(_translateFrom["Address"] != null && _translateFrom["Address"].ToString().Length > 0)
		{
		temp.Address = Convert.ToString(_translateFrom["Address"]);
		}
		else
		{
		temp.Address = String.Empty;
		}
		if(_translateFrom["City"] != null && _translateFrom["City"].ToString().Length > 0)
		{
		temp.City = Convert.ToString(_translateFrom["City"]);
		}
		else
		{
		temp.City = String.Empty;
		}
		if(_translateFrom["Region"] != null && _translateFrom["Region"].ToString().Length > 0)
		{
		temp.Region = Convert.ToString(_translateFrom["Region"]);
		}
		else
		{
		temp.Region = String.Empty;
		}
		if(_translateFrom["PostalCode"] != null && _translateFrom["PostalCode"].ToString().Length > 0)
		{
		temp.PostalCode = Convert.ToString(_translateFrom["PostalCode"]);
		}
		else
		{
		temp.PostalCode = String.Empty;
		}
		if(_translateFrom["OfficePhone"] != null && _translateFrom["OfficePhone"].ToString().Length > 0)
		{
		temp.OfficePhone = Convert.ToString(_translateFrom["OfficePhone"]);
		}
		else
		{
		temp.OfficePhone = String.Empty;
		}
		if(_translateFrom["CellPhone"] != null && _translateFrom["CellPhone"].ToString().Length > 0)
		{
		temp.CellPhone = Convert.ToString(_translateFrom["CellPhone"]);
		}
		else
		{
		temp.CellPhone = String.Empty;
		}
		if(_translateFrom["Title"] != null && _translateFrom["Title"].ToString().Length > 0)
		{
		temp.Title = Convert.ToString(_translateFrom["Title"]);
		}
		else
		{
		temp.Title = String.Empty;
		}
		return temp;
	}
	private IUserProfile TranslateSearchUserProfileFromDb(DataRow  _translateFrom)
	{
		IUserProfile temp = new UserProfile();
		temp.Action = T2.WebSecurity.Common.MyEnum.Actions.None.ToString();
		if(_translateFrom["UserName"] != null && _translateFrom["UserName"].ToString().Length > 0)
		{
		temp.UserName = Convert.ToString(_translateFrom["UserName"]);
		}
		else
		{
		temp.UserName = String.Empty;
		}
		if(_translateFrom["FirstName"] != null && _translateFrom["FirstName"].ToString().Length > 0)
		{
		temp.FirstName = Convert.ToString(_translateFrom["FirstName"]);
		}
		else
		{
		temp.FirstName = String.Empty;
		}
		if(_translateFrom["LastName"] != null && _translateFrom["LastName"].ToString().Length > 0)
		{
		temp.LastName = Convert.ToString(_translateFrom["LastName"]);
		}
		else
		{
		temp.LastName = String.Empty;
		}
		if(_translateFrom["Address"] != null && _translateFrom["Address"].ToString().Length > 0)
		{
		temp.Address = Convert.ToString(_translateFrom["Address"]);
		}
		else
		{
		temp.Address = String.Empty;
		}
		if(_translateFrom["City"] != null && _translateFrom["City"].ToString().Length > 0)
		{
		temp.City = Convert.ToString(_translateFrom["City"]);
		}
		else
		{
		temp.City = String.Empty;
		}
		if(_translateFrom["Region"] != null && _translateFrom["Region"].ToString().Length > 0)
		{
		temp.Region = Convert.ToString(_translateFrom["Region"]);
		}
		else
		{
		temp.Region = String.Empty;
		}
		if(_translateFrom["PostalCode"] != null && _translateFrom["PostalCode"].ToString().Length > 0)
		{
		temp.PostalCode = Convert.ToString(_translateFrom["PostalCode"]);
		}
		else
		{
		temp.PostalCode = String.Empty;
		}
		if(_translateFrom["OfficePhone"] != null && _translateFrom["OfficePhone"].ToString().Length > 0)
		{
		temp.OfficePhone = Convert.ToString(_translateFrom["OfficePhone"]);
		}
		else
		{
		temp.OfficePhone = String.Empty;
		}
		if(_translateFrom["CellPhone"] != null && _translateFrom["CellPhone"].ToString().Length > 0)
		{
		temp.CellPhone = Convert.ToString(_translateFrom["CellPhone"]);
		}
		else
		{
		temp.CellPhone = String.Empty;
		}
		if(_translateFrom["Title"] != null && _translateFrom["Title"].ToString().Length > 0)
		{
		temp.Title = Convert.ToString(_translateFrom["Title"]);
		}
		else
		{
		temp.Title = String.Empty;
		}
		return temp;
	}
		public IDictionary<string,Int32> UserProfile_Add(IUserProfile  _userprofile)
		{
			IDictionary<string, int> RetDict = new Dictionary<string, int>();
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("UserProfile_ins", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_UserId = DataHelpers.GetParameter("UserId", SqlDbType.Int, ParameterDirection.Output, 4) ;
			sp_UserId.Value = _userprofile.UserId;
			cmd.Parameters.Add(sp_UserId);

			SqlParameter sp_UserName = DataHelpers.GetParameter("UserName", SqlDbType.NVarChar, ParameterDirection.Input, 30) ;
			sp_UserName.Value = _userprofile.UserName;
			cmd.Parameters.Add(sp_UserName);

			SqlParameter sp_CreatedBy = DataHelpers.GetParameter("CreatedBy", SqlDbType.VarChar, ParameterDirection.Input, 50) ;
			sp_CreatedBy.Value = _userprofile.CreatedBy;
			cmd.Parameters.Add(sp_CreatedBy);

			SqlParameter sp_CreatedDt = DataHelpers.GetParameter("CreatedDt", SqlDbType.DateTime, ParameterDirection.Input, 13) ;
			sp_CreatedDt.Value = _userprofile.CreatedDt;
			cmd.Parameters.Add(sp_CreatedDt);

			SqlParameter sp_ModifiedBy = DataHelpers.GetParameter("ModifiedBy", SqlDbType.VarChar, ParameterDirection.Input, 50) ;
			sp_ModifiedBy.Value = _userprofile.ModifiedBy;
			cmd.Parameters.Add(sp_ModifiedBy);

			SqlParameter sp_ModifiedDt = DataHelpers.GetParameter("ModifiedDt", SqlDbType.DateTime, ParameterDirection.Input, 13) ;
			sp_ModifiedDt.Value = _userprofile.ModifiedDt;
			cmd.Parameters.Add(sp_ModifiedDt);

			SqlParameter sp_IsActive = DataHelpers.GetParameter("IsActive", SqlDbType.Bit, ParameterDirection.Input, 1) ;
			sp_IsActive.Value = _userprofile.IsActive;
			cmd.Parameters.Add(sp_IsActive);

			SqlParameter sp_FirstName = DataHelpers.GetParameter("FirstName", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_FirstName.Value = _userprofile.FirstName;
			cmd.Parameters.Add(sp_FirstName);

			SqlParameter sp_LastName = DataHelpers.GetParameter("LastName", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_LastName.Value = _userprofile.LastName;
			cmd.Parameters.Add(sp_LastName);

			SqlParameter sp_Address = DataHelpers.GetParameter("Address", SqlDbType.NVarChar, ParameterDirection.Input, 250) ;
			sp_Address.Value = _userprofile.Address;
			cmd.Parameters.Add(sp_Address);

			SqlParameter sp_City = DataHelpers.GetParameter("City", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_City.Value = _userprofile.City;
			cmd.Parameters.Add(sp_City);

			SqlParameter sp_Region = DataHelpers.GetParameter("Region", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_Region.Value = _userprofile.Region;
			cmd.Parameters.Add(sp_Region);

			SqlParameter sp_PostalCode = DataHelpers.GetParameter("PostalCode", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_PostalCode.Value = _userprofile.PostalCode;
			cmd.Parameters.Add(sp_PostalCode);

			SqlParameter sp_OfficePhone = DataHelpers.GetParameter("OfficePhone", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_OfficePhone.Value = _userprofile.OfficePhone;
			cmd.Parameters.Add(sp_OfficePhone);

			SqlParameter sp_CellPhone = DataHelpers.GetParameter("CellPhone", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_CellPhone.Value = _userprofile.CellPhone;
			cmd.Parameters.Add(sp_CellPhone);

			SqlParameter sp_Title = DataHelpers.GetParameter("Title", SqlDbType.NVarChar, ParameterDirection.Input, 100) ;
			sp_Title.Value = _userprofile.Title;
			cmd.Parameters.Add(sp_Title);

			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);

			int RetVal = cmd.ExecuteNonQuery();
			KeyValuePair<string, int> KVPRetVal = new KeyValuePair<string, int>("Return_Value", RetVal);
			RetDict.Add(KVPRetVal);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			KeyValuePair<string, int> RARetVal = new KeyValuePair<string, int>("Rows_Affected", Affected);
			RetDict.Add(RARetVal);
			int PkUserId = Convert.ToInt32(sp_UserId.Value);
			KeyValuePair<string, int> PKUserId = new KeyValuePair<string, int>("PKUserId", PkUserId);
			RetDict.Add(PKUserId);
			return RetDict;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			}
			}
		}
		public IDictionary<string,Int32> UserProfile_Modify(IUserProfile  _userprofile)
		{
			IDictionary<string, int> RetDict = new Dictionary<string, int>();
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("UserProfile_udt", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_UserId = DataHelpers.GetParameter("UserId", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_UserId.Value = _userprofile.UserId;
			cmd.Parameters.Add(sp_UserId);

			SqlParameter sp_UserName = DataHelpers.GetParameter("UserName", SqlDbType.NVarChar, ParameterDirection.Input, 30) ;
			sp_UserName.Value = _userprofile.UserName;
			cmd.Parameters.Add(sp_UserName);

			SqlParameter sp_TS = DataHelpers.GetParameter("TS", SqlDbType.Timestamp, ParameterDirection.Input, 8) ;
			sp_TS.Value = _userprofile.TS;
			cmd.Parameters.Add(sp_TS);

			SqlParameter sp_CreatedBy = DataHelpers.GetParameter("CreatedBy", SqlDbType.VarChar, ParameterDirection.Input, 50) ;
			sp_CreatedBy.Value = _userprofile.CreatedBy;
			cmd.Parameters.Add(sp_CreatedBy);

			SqlParameter sp_CreatedDt = DataHelpers.GetParameter("CreatedDt", SqlDbType.DateTime, ParameterDirection.Input, 13) ;
			sp_CreatedDt.Value = _userprofile.CreatedDt;
			cmd.Parameters.Add(sp_CreatedDt);

			SqlParameter sp_ModifiedBy = DataHelpers.GetParameter("ModifiedBy", SqlDbType.VarChar, ParameterDirection.Input, 50) ;
			sp_ModifiedBy.Value = _userprofile.ModifiedBy;
			cmd.Parameters.Add(sp_ModifiedBy);

			SqlParameter sp_ModifiedDt = DataHelpers.GetParameter("ModifiedDt", SqlDbType.DateTime, ParameterDirection.Input, 13) ;
			sp_ModifiedDt.Value = _userprofile.ModifiedDt;
			cmd.Parameters.Add(sp_ModifiedDt);

			SqlParameter sp_IsActive = DataHelpers.GetParameter("IsActive", SqlDbType.Bit, ParameterDirection.Input, 1) ;
			sp_IsActive.Value = _userprofile.IsActive;
			cmd.Parameters.Add(sp_IsActive);

			SqlParameter sp_FirstName = DataHelpers.GetParameter("FirstName", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_FirstName.Value = _userprofile.FirstName;
			cmd.Parameters.Add(sp_FirstName);

			SqlParameter sp_LastName = DataHelpers.GetParameter("LastName", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_LastName.Value = _userprofile.LastName;
			cmd.Parameters.Add(sp_LastName);

			SqlParameter sp_Address = DataHelpers.GetParameter("Address", SqlDbType.NVarChar, ParameterDirection.Input, 250) ;
			sp_Address.Value = _userprofile.Address;
			cmd.Parameters.Add(sp_Address);

			SqlParameter sp_City = DataHelpers.GetParameter("City", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_City.Value = _userprofile.City;
			cmd.Parameters.Add(sp_City);

			SqlParameter sp_Region = DataHelpers.GetParameter("Region", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_Region.Value = _userprofile.Region;
			cmd.Parameters.Add(sp_Region);

			SqlParameter sp_PostalCode = DataHelpers.GetParameter("PostalCode", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_PostalCode.Value = _userprofile.PostalCode;
			cmd.Parameters.Add(sp_PostalCode);

			SqlParameter sp_OfficePhone = DataHelpers.GetParameter("OfficePhone", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_OfficePhone.Value = _userprofile.OfficePhone;
			cmd.Parameters.Add(sp_OfficePhone);

			SqlParameter sp_CellPhone = DataHelpers.GetParameter("CellPhone", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_CellPhone.Value = _userprofile.CellPhone;
			cmd.Parameters.Add(sp_CellPhone);

			SqlParameter sp_Title = DataHelpers.GetParameter("Title", SqlDbType.NVarChar, ParameterDirection.Input, 100) ;
			sp_Title.Value = _userprofile.Title;
			cmd.Parameters.Add(sp_Title);

			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			int RetVal = cmd.ExecuteNonQuery();
			KeyValuePair<string, int> KVPRetVal = new KeyValuePair<string, int>("Return_Value", RetVal);
			RetDict.Add(KVPRetVal);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			KeyValuePair<string, int> RARetVal = new KeyValuePair<string, int>("Rows_Affected", Affected);
			RetDict.Add(RARetVal);
			int PkUserId = Convert.ToInt32(sp_UserId.Value);
			KeyValuePair<string, int> PKUserId = new KeyValuePair<string, int>("PKUserId", PkUserId);
			RetDict.Add(PKUserId);
			return RetDict;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			}
			}
		}
		public IDictionary<string, int> UserProfile_Remove(IUserProfile  _userprofile)
		{
			IDictionary<string, int> RetDict = new Dictionary<string, int>();
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("UserProfile_del", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_UserId = DataHelpers.GetParameter("UserId", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_UserId.Value = _userprofile.UserId;
			cmd.Parameters.Add(sp_UserId);

			SqlParameter sp_TS = DataHelpers.GetParameter("TS", SqlDbType.Timestamp, ParameterDirection.Input, 8) ;
			sp_TS.Value = _userprofile.TS;
			cmd.Parameters.Add(sp_TS);

			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			int RetVal = cmd.ExecuteNonQuery();
			KeyValuePair<string, int> KVPRetVal = new KeyValuePair<string, int>("Return_Value", RetVal);
			RetDict.Add(KVPRetVal);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			KeyValuePair<string, int> RARetVal = new KeyValuePair<string, int>("Rows_Affected", Affected);
			RetDict.Add(RARetVal);
			return RetDict;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}
		public IUserProfile UserProfile_GetOne(IUserProfile  _userprofile)
		{
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("UserProfile_GetOne", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_UserId = DataHelpers.GetParameter("UserId", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_UserId.Value = _userprofile.UserId;
			cmd.Parameters.Add(sp_UserId);

			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			IUserProfile RetVal = new UserProfile();
			if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
			RetVal = TranslateUserProfileFromDb(ds.Tables[0].Rows[0]);
			RetVal.TotalRows = Affected;
			}
			return RetVal;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}
		public List<IUserProfile> UserProfile_GetAll(IUserProfile  _userprofile)
		{
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("UserProfile_GetAll", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_PageSize = DataHelpers.GetParameter("PageSize", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageSize.Value = _userprofile.PageSize;
			cmd.Parameters.Add(sp_PageSize);
			SqlParameter sp_PageNumber = DataHelpers.GetParameter("PageNumber", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageNumber.Value = _userprofile.PageNumber;
			cmd.Parameters.Add(sp_PageNumber);
			SqlParameter sp_SortColumn = DataHelpers.GetParameter("SortColumn", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_SortColumn.Value = _userprofile.SortColumn;
			cmd.Parameters.Add(sp_SortColumn);
			SqlParameter sp_SortType = DataHelpers.GetParameter("SortType", SqlDbType.NVarChar, ParameterDirection.Input, 10) ;
			sp_SortType.Value = _userprofile.SortType;
			cmd.Parameters.Add(sp_SortType);
			SqlParameter sp_TotalRows = DataHelpers.GetParameter("TotalRows", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_TotalRows);
			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			List<IUserProfile> tempList = new List<IUserProfile>();
			
			foreach(DataTable tb in ds.Tables)
			{
				foreach(DataRow r in tb.Rows)
				{
                    IUserProfile temp = TranslateUserProfileFromDb(r);
					temp.TotalRows = Convert.ToInt32(sp_TotalRows.Value);
					
					tempList.Add(temp);
				}
			}
			return tempList;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}
		public List<IUserProfile> UserProfile_Search(IUserProfileSearch _userprofile)
		{
			string connect = ConfigurationManager.ConnectionStrings["WebSecurity"].ToString();
			SqlConnection db = new SqlConnection(connect);
			SqlCommand cmd = new SqlCommand("UserProfile_Search", db);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection.Open();
			try
			{
			SqlParameter sp_UserName = DataHelpers.GetParameter("UserName", SqlDbType.NVarChar, ParameterDirection.Input, 30) ;
			sp_UserName.Value = _userprofile.UserName;
			cmd.Parameters.Add(sp_UserName);

			SqlParameter sp_FirstName = DataHelpers.GetParameter("FirstName", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_FirstName.Value = _userprofile.FirstName;
			cmd.Parameters.Add(sp_FirstName);

			SqlParameter sp_LastName = DataHelpers.GetParameter("LastName", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_LastName.Value = _userprofile.LastName;
			cmd.Parameters.Add(sp_LastName);

			SqlParameter sp_Address = DataHelpers.GetParameter("Address", SqlDbType.NVarChar, ParameterDirection.Input, 250) ;
			sp_Address.Value = _userprofile.Address;
			cmd.Parameters.Add(sp_Address);

			SqlParameter sp_City = DataHelpers.GetParameter("City", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_City.Value = _userprofile.City;
			cmd.Parameters.Add(sp_City);

			SqlParameter sp_Region = DataHelpers.GetParameter("Region", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_Region.Value = _userprofile.Region;
			cmd.Parameters.Add(sp_Region);

			SqlParameter sp_PostalCode = DataHelpers.GetParameter("PostalCode", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_PostalCode.Value = _userprofile.PostalCode;
			cmd.Parameters.Add(sp_PostalCode);

			SqlParameter sp_OfficePhone = DataHelpers.GetParameter("OfficePhone", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_OfficePhone.Value = _userprofile.OfficePhone;
			cmd.Parameters.Add(sp_OfficePhone);

			SqlParameter sp_CellPhone = DataHelpers.GetParameter("CellPhone", SqlDbType.NVarChar, ParameterDirection.Input, 25) ;
			sp_CellPhone.Value = _userprofile.CellPhone;
			cmd.Parameters.Add(sp_CellPhone);

			SqlParameter sp_Title = DataHelpers.GetParameter("Title", SqlDbType.NVarChar, ParameterDirection.Input, 100) ;
			sp_Title.Value = _userprofile.Title;
			cmd.Parameters.Add(sp_Title);

			SqlParameter sp_PageSize = DataHelpers.GetParameter("PageSize", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageSize.Value = _userprofile.PageSize;
			cmd.Parameters.Add(sp_PageSize);
			SqlParameter sp_PageNumber = DataHelpers.GetParameter("PageNumber", SqlDbType.Int, ParameterDirection.Input, 4) ;
			sp_PageNumber.Value = _userprofile.PageNumber;
			cmd.Parameters.Add(sp_PageNumber);
			SqlParameter sp_SortColumn = DataHelpers.GetParameter("SortColumn", SqlDbType.NVarChar, ParameterDirection.Input, 50) ;
			sp_SortColumn.Value = _userprofile.SortColumn;
			cmd.Parameters.Add(sp_SortColumn);
			SqlParameter sp_SortType = DataHelpers.GetParameter("SortType", SqlDbType.NVarChar, ParameterDirection.Input, 10) ;
			sp_SortType.Value = _userprofile.SortType;
			cmd.Parameters.Add(sp_SortType);
			SqlParameter sp_TotalRows = DataHelpers.GetParameter("TotalRows", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_TotalRows);
			SqlParameter sp_RowsAffected = DataHelpers.GetParameter("RowsAffected", SqlDbType.Int, ParameterDirection.Output, 4) ;
			cmd.Parameters.Add(sp_RowsAffected);
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);
			int Affected = Convert.ToInt32(sp_RowsAffected.Value);
			List<IUserProfile> tempList = new List<IUserProfile>();
			foreach(DataTable tb in ds.Tables)
			{
				foreach(DataRow r in tb.Rows)
				{
					IUserProfile temp = new UserProfile();
					temp = TranslateUserProfileFromDb(r);
					tempList.Add(temp);
				}
			}
			return tempList;
			}
			catch(Exception ex)
			{
			throw ex;
			}
			finally
			{
			if (cmd != null)
			{
			cmd.Connection.Close();
			cmd.Dispose();
			db = null;
			}
			}
		}

    }
}
